﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DbStreamConverter
{
    public static class DbProviderRepository
    {
        public static readonly Dictionary<string, Type> ProviderList;

        static DbProviderRepository()
        {
            ProviderList = new Dictionary<string, Type>();
            ProviderList.Add("System.Data.SqlClient", typeof(SqlClientDbProvider));
            ProviderList.Add("System.Data.OleDb", typeof(OleDbProvider));
            ProviderList.Add("System.Data.SqlServerCe.4.0", typeof(SqlCeClientDbProvider));
            var odp = Type.GetType("DbStreamConverter.Odp.OdpDbProvider, DbStreamConverter.Odp");
            if (odp != null)
            {
                ProviderList.Add("Oracle.DataAccess.Client", odp);
            }
        }

        public static DbProvider GetDbSchemaProvider(string providerName, string connectionString)
        {
            var prv = (DbProvider)Activator.CreateInstance(ProviderList[providerName]);
            prv.Config = new ConnectionStringSettings(null, connectionString, providerName);
            return prv;
        }
    }

    public abstract class DbProvider
    {
        public ConnectionStringSettings Config { get; set; }
        public DbProviderFactory Factory { get; set; }


        public virtual List<string> GetTablesViews()
        {
            DbConnection cnct = Factory.CreateConnection();
            cnct.ConnectionString = Config.ConnectionString;

            List<string> ds = new List<string>();
            try
            {
                cnct.Open();
                return cnct.GetSchema("Tables").Rows.Cast<DataRow>()
                    .Select(x => GetDbTableName(x)).ToList();
            }
            finally
            {
                cnct.Close();
            }
        }

        protected virtual string GetDbTableName(DataRow row)
        {
            return (string)row["TABLE_NAME"];
        }


        public virtual string GetTableName(string tblName)
        {
            return tblName;
        }

        public Dictionary<string, Type> GetTableColumn(string table)
        {

            DbConnection cnct = Factory.CreateConnection();
            cnct.ConnectionString = Config.ConnectionString;
            DbCommand cmd = cnct.CreateCommand();
            if (table.StartsWith("select ", StringComparison.InvariantCultureIgnoreCase))
            {
                cmd.CommandText = table;
            }
            else
            {
                cmd.CommandText = string.Format("Select * from {0} where 1=0", GetTableName(table));
            }
            try
            {
                cnct.Open();
                using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly))
                {
                    var tbl = reader.GetSchemaTable();
                    var rows = tbl.Rows.Cast<DataRow>().Where(x=>!tbl.Columns.Contains("IsHidden") || !(bool)x["IsHidden"]);
                    return rows.ToDictionary(x => (string)x["ColumnName"], x => GetDataType(x));
                }
            }
            finally
            {
                cnct.Close();
            }
        }

        private Type GetDataType(DataRow row)
        {
            var allowDbNull = (bool)row["AllowDBNull"];
            var dbType = (Type)row["DataType"];
            if (allowDbNull && dbType.IsValueType)
            {
                return typeof(Nullable<>).MakeGenericType(dbType);
            }
            else
            {
                return dbType;
            }
        }

        public abstract void BulkCopy<T>(IEnumerable<T> items, string destinationTableName, List<string> propList, int timeout = 0);

        public virtual void CreateTempTable(string tableName, string seletStat)
        {
            DbConnection cnct = Factory.CreateConnection();
            cnct.ConnectionString = Config.ConnectionString;
            DbCommand cmd = cnct.CreateCommand();
            cmd.CommandText = string.Format("select * into {0} from ({1}) source where 1=0", GetTableName(tableName), seletStat);
            try
            {
                cnct.Open();
                cmd.ExecuteNonQuery();
            }
            finally
            {
                cnct.Close();
            }
        }

        public virtual void Truncate(string tableName)
        {
            DbConnection cnct = Factory.CreateConnection();
            cnct.ConnectionString = Config.ConnectionString;
            DbCommand cmd = cnct.CreateCommand();
            cmd.CommandText = "Truncate table " + GetTableName(tableName);
            try
            {
                cnct.Open();
                cmd.ExecuteNonQuery();
            }
            finally
            {
                cnct.Close();
            }
        } 
    }

    public class SqlClientDbProvider : DbProvider
    {
        public SqlClientDbProvider()
        {
            Factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
        }

        protected override string GetDbTableName(DataRow row)
        {
            return "[" + (string)row["TABLE_SCHEMA"] + "].[" + (string)row["TABLE_NAME"] + "]";
        }

        public override void BulkCopy<T>(IEnumerable<T> items, string destinationTableName, List<string> propList, int timeout = 0)
        {
            destinationTableName = destinationTableName ?? typeof(T).Name;
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(Config.ConnectionString))
            {
                foreach (var prop in propList)
                {
                    bulkCopy.ColumnMappings.Add(prop, prop);
                }
                bulkCopy.DestinationTableName = destinationTableName;
                bulkCopy.BulkCopyTimeout = timeout;
                bulkCopy.WriteToServer(new BulkCopyDataReader<T>(items, propList));
            }
        }


    }
    public class SqlCeClientDbProvider : OleDbProvider
    {
        public SqlCeClientDbProvider()
        {
            Factory = DbProviderFactories.GetFactory("System.Data.SqlServerCe.4.0");
        }
    }



    public class OleDbProvider : DbProvider
    {
        public OleDbProvider()
        {
            Factory = DbProviderFactories.GetFactory("System.Data.OleDb");
        }

        public override string GetTableName(string tblName)
        {
            return "[" + tblName + "]";
        }
        public override void BulkCopy<T>(IEnumerable<T> items, string destinationTableName, List<string> propList, int timeout = 0)
        {
            DbConnection cnct = Factory.CreateConnection();
            cnct.ConnectionString = Config.ConnectionString;
 

            var propDescs = propList.Select(x=> typeof(T).GetProperty(x)).ToList(); 
            try
            {
                cnct.Open();
                using (DbCommand cmd = cnct.CreateCommand())
                {
                    cmd.CommandText = "insert into " + GetTableName(destinationTableName) + " (" +
                        string.Join(",", propList.Select(x => "[" + x + "]"))
                        + ") values (" +
                            string.Join(",", propList.Select(x => "?"))
                        + ")";
                    foreach (var item in items)
                    {
                        cmd.Parameters.Clear();
                        foreach (var prop in propDescs)
                        {
                            var para = Factory.CreateParameter();
                            para.Value = prop.GetValue(item, null) ?? DBNull.Value;
                            cmd.Parameters.Add(para);
                        }
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            finally
            {
                cnct.Close();
            }
           
        }

        public override void Truncate(string tableName)
        {
            DbConnection cnct = Factory.CreateConnection();
            cnct.ConnectionString = Config.ConnectionString;
            DbCommand cmd = cnct.CreateCommand();
            cmd.CommandText = "Delete from " + GetTableName(tableName);
            try
            {
                cnct.Open();
                cmd.ExecuteNonQuery();
            }
            finally
            {
                cnct.Close();
            }
        }
    }

    
}
